IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.ScriptIndexesTable'))
BEGIN
	DROP PROCEDURE MD3.ScriptIndexesTable
END
GO

CREATE Procedure MD3.ScriptIndexesTable

AS  
BEGIN

/*

	Generates a SQL script that can be saved to the Indexes folder, in MasterData format, that is used as the basis for all 
	index manipulation.



*/


PRINT '/*'
PRINT '	This script holds the MasterData about our Indexes'
PRINT '	Simply modify this metadata and rerun the MD3 deployment and any Index changes'
PRINT '		will be reflected in your database.  '
PRINT ''
PRINT '	To add a new Index ...Add a new INSERT line to the file.'
PRINT '	To modify a Index...Modify the values of the EXISTING Index row'
PRINT '	To delete a Index...Set the DropIndex flag to 1.  Do not actually delete the row.'
PRINT ''
PRINT ' This script can be automatically regenerated by running MD3.ScriptIndexesTable'
PRINT '*/'

SET NOCOUNT ON
PRINT 'SET NOCOUNT ON'
PRINT '--remove all old entries from table'
PRINT 'TRUNCATE TABLE MD3.Indexes'
PRINT ''
PRINT '--add new entries to table'


DECLARE @exec_str VARCHAR(MAX), @new_str VARCHAR(MAX), @quote VARCHAR(MAX) = '''';
SELECT @exec_str = 'INSERT INTO MD3.Indexes (IndexName,SchemaName,TableName,KeyColumns,IncludedColumns,IsClustered,IsUnique,FGorPartitionScheme,IdxFillFactor,IsPadded,CompressionSetting,DropIndex ) VALUES('

DECLARE 
	@IndexName VARCHAR(256),
	@SchemaName VARCHAR(256),
	@TableName VARCHAR(256) ,
	@KeyColumns VARCHAR(MAX) ,
	@IncludedColumns VARCHAR(MAX),
    @IsClustered	VARCHAR(256) , 
	@IsUnique	VARCHAR(256) , 
	@FGorPartitionScheme VARCHAR(MAX),
	@IdxFillFactor VARCHAR(256) ,
	@IsPadded VARCHAR(256) ,
	@CompressionSetting VARCHAR(50),
	@DropIndex VARCHAR(256) 


DECLARE dave CURSOR FOR 
	SELECT 
		IndexName,SchemaName,TableName,KeyColumns,IncludedColumns,IsClustered,IsUnique,FGorPartitionScheme,IdxFillFactor,IsPadded,CompressionSetting,DropIndex 
	FROM MD3.Indexes
	ORDER BY SchemaName, TableName
OPEN dave
FETCH NEXT FROM dave INTO 
	@IndexName ,
	@SchemaName ,
	@TableName  ,
	@KeyColumns ,
	@IncludedColumns,
    @IsClustered	 , 
	@IsUnique	, 
	@FGorPartitionScheme ,
	@IdxFillFactor  ,
	@IsPadded  ,
	@CompressionSetting,
	@DropIndex 
WHILE (@@FETCH_STATUS = 0)
BEGIN

	SELECT @new_str  = @exec_str + '''' + @IndexName + ''',''' + @SchemaName + ''',''' + @TableName + ''',''' + @KeyColumns + ''',' + CASE WHEN @IncludedColumns IS NULL THEN 'NULL' ELSE '''' + @IncludedColumns + ''','  END + ','
	SELECT @new_str += @IsClustered + ',' + @IsUnique + ',''' + @FGorPartitionScheme + ''',' + @IdxFillFactor + ',' + @IsPadded + ','''
	SELECT @new_str += @CompressionSetting + ''',' 
	SELECT @new_str += @DropIndex +');'
	PRINT @new_str
	
	FETCH NEXT FROM dave INTO 
		@IndexName ,
		@SchemaName ,
		@TableName  ,
		@KeyColumns ,
		@IncludedColumns,
		@IsClustered	 , 
		@IsUnique	, 
		@FGorPartitionScheme ,
		@IdxFillFactor  ,
		@IsPadded  ,
		@CompressionSetting,
		@DropIndex 
END;
CLOSE dave
DEALLOCATE dave


PRINT '--now building Indexes'
PRINT 'EXEC MD3.RunIndexes'
PRINT 'GO'
PRINT 'SET NOCOUNT OFF'

END
GO
